﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using IRepository;
using System.Data;
using System.Data.SqlClient;
using DBHelper;
namespace Repository
{
    public class SqlRoleRepository : IRoleRepository
    {
        public bool Exists(string id)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select count(*) from BaseRole where ID=@ID");
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.VarChar, 36) };
            parameters[0].Value = id;
            int i = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sbQuery.ToString(), parameters));
            return i > 0 ? true : false;
        }

        public bool Add(BaseEntity entity)
        {
            Role role = (Role)entity;
            StringBuilder sbInsert = new StringBuilder();
            sbInsert.Append("insert into BaseRole(ID,Name,Description,Creator,CreateTime,Updator,UpdateTime,Enabled,Remark)");
            sbInsert.Append("values(@ID,@Name,@Description,@Creator,@CreateTime,@Updator,@UpdateTime,@Enabled,@Remark)");
            SqlParameter[] parameters = {
                new SqlParameter("@ID", SqlDbType.VarChar,36),
                new SqlParameter("@Name", SqlDbType.NVarChar,20),
                new SqlParameter("@Description", SqlDbType.NVarChar,200),
                new SqlParameter("@Creator", SqlDbType.NVarChar,50),
                new SqlParameter("@CreateTime", SqlDbType.DateTime),
                new SqlParameter("@Updator", SqlDbType.NVarChar,50),
                new SqlParameter("@UpdateTime", SqlDbType.DateTime),
                new SqlParameter("@Enabled", SqlDbType.Bit,1),
                new SqlParameter("@Remark", SqlDbType.NVarChar,200)};
            parameters[0].Value = role.ID;
            parameters[1].Value = role.Name;
            parameters[2].Value = role.Description;
            parameters[3].Value = role.Creator;
            parameters[4].Value = role.CreateTime;
            parameters[5].Value = role.Updator;
            parameters[6].Value = role.UpdateTime;
            parameters[7].Value = role.Enabled;
            parameters[8].Value = role.Remark;

            int i = SqlHelper.ExecuteNonQuery(CommandType.Text, sbInsert.ToString(), parameters);
            return i > 0 ? true : false;
        }

        public bool Update(BaseEntity entity)
        {
            Role role = (Role)entity;
            StringBuilder sbUpdate = new StringBuilder();
            sbUpdate.Append("update BaseRole set ");
            sbUpdate.Append("Name=@Name,");
            sbUpdate.Append("Description=@Description,");
            sbUpdate.Append("Updator=@Updator,");
            sbUpdate.Append("UpdateTime=@UpdateTime,");
            sbUpdate.Append("Enabled=@Enabled,");
            sbUpdate.Append("Remark=@Remark");
            sbUpdate.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@Name", SqlDbType.NVarChar,20),
                    new SqlParameter("@Description", SqlDbType.VarChar,200),
                    new SqlParameter("@Updator", SqlDbType.NVarChar,50),
                    new SqlParameter("@UpdateTime", SqlDbType.DateTime),
                    new SqlParameter("@Enabled", SqlDbType.Bit,1),
                    new SqlParameter("@Remark", SqlDbType.NVarChar,200),
                    new SqlParameter("@ID", SqlDbType.VarChar,36)};
            parameters[0].Value = role.Name;
            parameters[1].Value = role.Description;
            parameters[2].Value = role.Updator;
            parameters[3].Value = role.UpdateTime;
            parameters[4].Value = role.Enabled;
            parameters[5].Value = role.Remark;
            parameters[6].Value = role.ID;
            int i = SqlHelper.ExecuteNonQuery(CommandType.Text, sbUpdate.ToString(), parameters);
            return i > 0 ? true : false;
            
        }

        public bool Delete(string id)
        {
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("delete from BaseRole where ID=@ID");
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.VarChar, 36) };
            parameters[0].Value = id;
            int i = SqlHelper.ExecuteNonQuery(CommandType.Text, sbDelete.ToString(), parameters);
            return i > 0 ? true : false;
        }

        public bool Delete(List<string> listID)
        {
            StringBuilder sbDelete = new StringBuilder();
            sbDelete.Append("delete from BaseRole where ID in(");
            for (int i = 0; i < listID.Count; i++)
            {
                if (i == 0)
                {
                    sbDelete.Append("'" + listID[i] + "'");
                }
                else
                {
                    sbDelete.Append(",'" + listID[i] + "'");
                }
            }
            sbDelete.Append(")");
            int j = SqlHelper.ExecuteNonQuery(CommandType.Text, sbDelete.ToString(), null);
            return j > 0 ? true : false;
        }

        public BaseEntity Get(string id)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select ID,Name,Description,Creator,CreateTime,Updator,UpdateTime,Enabled,Remark from BaseRole");
            sbQuery.Append(" where ID=@ID");
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.VarChar, 36) };
            parameters[0].Value = id;
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), parameters);
            Role role = new Role();
            while (dr.Read())
            {
                role =DataReaderToRole(dr);
            }
            dr.Close();
            return role;
        }

        public IQueryable<BaseEntity> GetList(string strWhere)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select ID,Name,Description,Creator,CreateTime,Updator,UpdateTime,Enabled,Remark from BaseRole");
            sbQuery.Append(" where (" + strWhere + ")");
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), null);
            List<Role> listRoles = new List<Role>();
            while (dr.Read())
            {
                listRoles.Add(DataReaderToRole(dr));
            }
            return listRoles.AsQueryable();
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select count(*) from BaseRole");
            sbQuery.Append(" where (" + strWhere + ")");
            return Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sbQuery.ToString(), null));
        }

        public IQueryable<BaseEntity> GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            strWhere = String.IsNullOrEmpty(strWhere.Trim()) ? "1=1" : strWhere;
            orderby = String.IsNullOrEmpty(orderby.Trim()) ? "Name desc" : orderby;
            StringBuilder sbQuery = new StringBuilder();
            sbQuery.Append("select t.*from(");
            sbQuery.AppendFormat("    select ROW_NUMBER() OVER(order by p.{0}) as RowNum, p.* from BaseRole p", orderby);
            sbQuery.AppendFormat("	where ({0})", strWhere);
            sbQuery.Append(") t");
            sbQuery.AppendFormat(" where t.RowNum between {0} and {1}", startIndex, endIndex);
            List<Role> listRoles = new List<Role>();
            SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sbQuery.ToString(), null);
            while (dr.Read())
            {
                listRoles.Add(DataReaderToRole(dr));

            }
            dr.Close();
            return listRoles.AsQueryable();
        }

        private Role DataReaderToRole(SqlDataReader dr)
        {
            Entities.Role model = new Entities.Role();
            if (dr != null)
            {
                if (dr["ID"] != null)
                {
                    model.ID = dr["ID"].ToString();
                }
                if (dr["Name"] != null)
                {
                    model.Name = dr["Name"].ToString();
                }
                if (dr["Description"] != null)
                {
                    model.Description = dr["Description"].ToString();
                }
                if (dr["Creator"] != null)
                {
                    model.Creator = dr["Creator"].ToString();
                }
                if (dr["CreateTime"] != null && dr["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(dr["CreateTime"].ToString());
                }
                if (dr["Updator"] != null)
                {
                    model.Updator = dr["Updator"].ToString();
                }
                if (dr["UpdateTime"] != null && dr["UpdateTime"].ToString() != "")
                {
                    model.UpdateTime = DateTime.Parse(dr["UpdateTime"].ToString());
                }
                if (dr["Enabled"] != null && dr["Enabled"].ToString() != "")
                {
                    if ((dr["Enabled"].ToString() == "1") || (dr["Enabled"].ToString().ToLower() == "true"))
                    {
                        model.Enabled = true;
                    }
                    else
                    {
                        model.Enabled = false;
                    }
                }
                if (dr["Remark"] != null)
                {
                    model.Remark = dr["Remark"].ToString();
                }
            }
            return model;
        }
    }
}
